﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using SQLToolbox.Types;
namespace SQLToolbox.DAL
{
    /// <summary>
    /// Executes scrips on SQLServer
    /// </summary>
    public class SqlClientScriptExecuter : IDatabaseScriptExecuter
    {
        #region IDatabaseScriptExecuter Members

        /// <summary>
        /// Executes the script.
        /// </summary>
        /// <param name="dcp">The Database Connection Properties.</param>
        /// <param name="script">The script.</param>
        public void ExecuteScriptNonQuery(DBConnectionProperties dcp, Script script)
        {
            var scsb = CreateConnectionString(dcp);
            ExcecuteScriptNonQuery(scsb.ConnectionString, script);
        }




        /// <summary>
        /// Executes the script.
        /// </summary>
        /// <param name="dcp">The Database Connection Properties.</param>
        /// <param name="script">The script.</param>
        /// <returns></returns>
        public IEnumerable<DataTable> ExecuteScript(DBConnectionProperties dcp, Script script)
        {
            var scsb = CreateConnectionString(dcp);
            return ExcecuteScript(scsb.ConnectionString, script);
        }


        #endregion

        private void ExcecuteScriptNonQuery(string connectionString, Script script)
        {
            SqlTransaction tran = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                try
                {
                    tran = conn.BeginTransaction();
                    foreach (string batch in script)
                    {
                        var command = conn.CreateCommand();
                        command.CommandType = CommandType.Text;
                        command.CommandText = batch;
                        command.Transaction = tran;
                        command.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch (SqlException e)
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }
                    throw new SQLTooboxDALException(string.Format("Error while executing script on database\r\nConnectionString:{0}\r\nScript:{1}", connectionString, script), e);
                }

            }
        }



        private IEnumerable<DataTable> ExcecuteScript(string connectionString, Script script)
        {
            List<DataTable> dtList = new List<DataTable>();
            SqlTransaction tran = null;

            using (SqlConnection conn = new SqlConnection(connectionString))
            {

                conn.Open();
                try
                {
                    tran = conn.BeginTransaction();

                    SqlDataAdapter da = new SqlDataAdapter();
                    foreach (string batch in script)
                    {
                        var command = conn.CreateCommand();
                        command.CommandType = CommandType.Text;
                        command.CommandText = batch;
                        command.Transaction = tran;
                        DataTable dt = new DataTable();
                        da.SelectCommand = command;
                        da.Fill(dt);
                        dtList.Add(dt);
                    }
                    tran.Commit();
                }
                catch (SqlException e)
                {
                    if (tran != null)
                    {
                        tran.Rollback();
                    }
                    throw new SQLTooboxDALException(e.Message, e);
                }
            }

            return dtList;
        }

        private static SqlConnectionStringBuilder CreateConnectionString(DBConnectionProperties dcp)
        {
            var scsb = new SqlConnectionStringBuilder
            {
                DataSource = dcp.ServerName,
                InitialCatalog = dcp.DatabaseName,
            };
            if (dcp.Credentials.IntegratedSecurity)
                scsb.IntegratedSecurity = true;
            else
            {
                scsb.UserID = dcp.Credentials.Username;
                scsb.Password = dcp.Credentials.Password;
                scsb.IntegratedSecurity = false;
            }
            return scsb;
        }
    }
}
